package com.auxgroup.hr.front.commons;

import java.util.*;


public interface Constant {
	
	String DEFAULT_CHARSET="utf-8";
	/**
	 * 版本号
	 */
	String HTTP_HEADER_X_VER="ver";
	
	/**
	 * 允许展示的图片格式
	 */
	public final static Set<String> ALLOWED_DISPLAY_IMG_FORMAT= new HashSet<String>(Arrays.asList("jpg","JPG","png","PNG","jpeg","JPEG","bmp","BMP"));

	/**
	 * 请休假考勤数据校验接口
	 */
	public final static String KQ_CHECK_URL = "http://hrservice.auxgroup.com/PSIGW/RESTListeningConnector/PSFT_HR/MOBILELEAVECHECKSUBMITPOST.v1/LeaveCheckSubmit";

	String SHEBAO_CENTER = "select rs_util.get_descr_company(rs_si_company, sysdate) " +
			"   from ps_rs_empl_si_tbl A" +
			"   where A.rs_si_type = '10'" +
			"     and A.effdt = (select max(effdt)" +
			"       from ps_rs_empl_si_tbl A1" +
			"           where A.effdt <= sysdate" +
			"            and A.emplid = A1.emplid and A.rs_si_type=A1.rs_si_type " +
			" 			 and A.EMPL_RCD=A1.EMPL_RCD) and emplid='{}'";
	String GONGJIJIN_CENTER = "select rs_util.get_descr_company(rs_si_company, sysdate) " +
			"   from ps_rs_empl_si_tbl A" +
			"   where A.rs_si_type = '60'" +
			"     and A.effdt = (select max(effdt)" +
			"       from ps_rs_empl_si_tbl A1" +
			"           where A.effdt <= sysdate" +
			"            and A.emplid = A1.emplid and A.rs_si_type=A1.rs_si_type " +
			" 			 and A.EMPL_RCD=A1.EMPL_RCD) and emplid='{}'";
	//薪酬信息查询
	String BASE_PAYROLL_INFO = "SELECT decode(( " +
			" SELECT a.descr " +
			"  FROM ps_c_sal_plan_tbl a " +
			" WHERE a.setid = job.business_unit " +
			"   AND a.c_aux_system = job.c_aux_system " +
			"   AND a.sal_admin_plan = job.sal_admin_plan " +
			"   AND a.effdt = ( " +
			" SELECT MAX(a1.effdt) " +
			"  FROM ps_c_sal_plan_tbl a1 " +
			" WHERE a.setid = a1.setid " +
			"   AND a.c_aux_system = a1.c_aux_system " +
			"   AND a1.sal_admin_plan = a.sal_admin_plan) " +
			"   AND a.eff_status = 'A'), NULL, ( " +
			" SELECT a.descr " +
			"  FROM ps_c_sal_plan_tbl a " +
			" WHERE a.setid = job.business_unit " +
			"   AND a.c_aux_system = '000' " +
			"   AND a.sal_admin_plan = job.sal_admin_plan " +
			"   AND a.effdt = ( " +
			" SELECT MAX(a1.effdt) " +
			"  FROM ps_c_sal_plan_tbl a1 " +
			" WHERE a.setid = a1.setid " +
			"   AND a.c_aux_system = a1.c_aux_system " +
			"   AND a1.sal_admin_plan = a.sal_admin_plan) " +
			"   AND a.eff_status = 'A'), ( " +
			" SELECT a.descr " +
			"  FROM ps_c_sal_plan_tbl a " +
			" WHERE a.setid = job.business_unit " +
			"   AND a.c_aux_system = job.c_aux_system " +
			"   AND a.sal_admin_plan = job.sal_admin_plan " +
			"   AND a.effdt = ( " +
			" SELECT MAX(a1.effdt) " +
			"  FROM ps_c_sal_plan_tbl a1 " +
			" WHERE a.setid = a1.setid " +
			"   AND a.c_aux_system = a1.c_aux_system " +
			"   AND a1.sal_admin_plan = a.sal_admin_plan) " +
			"   AND a.eff_status = 'A')) plan_desc, ( " +
			" SELECT jg.descr " +
			"  FROM ps_c_job_grade_vw jg " +
			" WHERE jg.c_job_grade = job.grade) grade_desc, ( " +
			" SELECT c.descr " +
			"  FROM ps_c_sal_plan_tbl a " +
			"  , ps_c_sal_grade_tbl b " +
			"  , ps_c_sal_step_tbl c " +
			" WHERE a.setid = b.setid " +
			"   AND a.sal_admin_plan = b.sal_admin_plan " +
			"   AND a.c_aux_system = b.c_aux_system " +
			"   AND a.effdt = b.effdt " +
			"   AND a.eff_status = 'A' " +
			"   AND a.setid = c.setid " +
			"   AND a.sal_admin_plan = c.sal_admin_plan " +
			"   AND a.c_aux_system = c.c_aux_system " +
			"   AND a.effdt = c.effdt " +
			"   AND b.grade = c.grade " +
			"   AND a.effdt = ( " +
			" SELECT MAX(a1.effdt) " +
			"  FROM ps_c_sal_plan_tbl a1 " +
			" WHERE a.setid = a1.setid " +
			"   AND a.sal_admin_plan = a1.sal_admin_plan " +
			"   AND a.c_aux_system = a1.c_aux_system " +
			"   AND a1.effdt <= sysdate) " +
			"   AND a.setid = job.business_unit " +
			"   AND b.sal_admin_plan = job.sal_admin_plan " +
			"   AND b.grade = job.grade " +
			"   AND c.step = job.step) step_desc, ( " +
			" SELECT x.descr " +
			"  FROM ps_gp_pygrp x " +
			"  WHERE x.gp_paygroup = job.gp_paygroup) gp_paygroup, rs_util.get_descr_company(job.c_calc_company, sysdate), " +
			"  rs_util.get_descr_company(job.c_fee_company, sysdate) , job.c_cost_center_desc, " +
			"  rs_util.get_descr_translate('C_PAY_TYPE', c_pay_type, sysdate), " +
			"  decode(c_pay_type, 'D', c_sal_mon_fixed, c_sal_mon_std), " +
			"  c_sal_year_float, c_sal_year_std, c_pay_daily_sal, c_pay_hour_sal " +
			"  FROM ps_c_job_curr_vw job" +
			"  WHERE emplid = '{}'" +
			"  AND empl_rcd = 0 ";

	//入职 报道日期查询
	String HIRE_DATE_QUERY = "SELECT C_FIRST_HIRE_DT, C_LAST_HIRE_DT,C_HRS_POST__DT " +
			"FROM " +
			"PS_C_PERS_DT_TBL WHERE EMPLID = '{}'";


	//工资单记录查询
	String PAY_SLIP_QUERY = "SELECT CAL_PRD_ID,A.CAL_RUN_ID, AMT1,AMT2,AMT3,AMT4,B.DESCR,A.RUN_TYPE " +
			"FROM " +
			"(SELECT CAL_PRD_ID,CAL_RUN_ID,SUM(C_AMT_177) AMT1,SUM(C_AMT_180) AMT2,SUM(C_AMT_130) AMT3 ,SUM(C_AMT_178) AMT4,RUN_TYPE " +
			"FROM PS_C_WA_PAY_RSLT WHERE EMPLID = '{}' AND PRD_BGN_DT >= to_date('{}','yyyy-MM-dd HH24:mi:ss')    AND PRD_END_DT <= to_date('{}','yyyy-MM-dd HH24:mi:ss')   " +
			"GROUP BY CAL_PRD_ID,CAL_RUN_ID,RUN_TYPE  " +
			"ORDER BY CAL_PRD_ID DESC) A LEFT JOIN " +
			"PS_GP_CAL_RUN B " +
			"ON A.CAL_RUN_ID = B.CAL_RUN_ID " +
			"WHERE B.RUN_FINALIZED_IND = 'Y' OR B.C_RUN_SRCH_SEC = 'Y'";

	String PERMISSION_PAYSLIP = "SELECT 'X' FROM PSROLEUSER WHERE ROLEUSER = '{}' AND ROLENAME = 'AUX_ROLE_GP_041'";
	//工资单名称
//	String PAYROLL_NAME = "SELECT DESCR FROM PS_GP_CAL_RUN WHERE CAL_RUN_ID = '{}'";


	//工资组查询
	String GP_PAYGROUP = "SELECT X.GP_PAYGROUP FROM PS_C_WA_PAY_RSLT A,PS_FP_PAY_FORMU_T1 X  WHERE  A.GP_PAYGROUP=X.GP_PAYGROUP AND X.BUSINESS_UNIT='SHARE' AND A.EMPLID = '{}'  AND A.CAL_RUN_ID = '{}'";

	//工资大类查询
	String PAYROLL_DETAIL_LV1 =
			"SELECT EMPLID," +
					"       C_YEAR," +
					"       CAL_RUN_ID," +
					"       DESCR," +
					"       SEQNO1," +
					"       FP_ITEM_NAME1," +
					"       SUM(decode(FP_OPERATOR2, '+', C_AMT, '-', -C_AMT, 0))" +
					"  FROM (SELECT A.EMPLID," +
					"               to_char(A.PRD_BGN_DT, 'yyyy') C_YEAR," +
					"               A.CAL_RUN_ID," +
					"               C.DESCR," +
					"               B.SEQNO1," +
					"               B.FP_ITEM_NAME1," +
					"               B.SEQNO2," +
					"               B.FP_ITEM_NAME2," +
					"               B.Fp_Operator2," +
					"               SUM(decode(B.FP_OPERATOR3," +
					"                          '+'," +
					"                          C_PAY_UTIL.GET_AMT_BY_EMPL_CAL_ID(A.EMPLID," +
					"                                                            A.CAL_RUN_ID," +
					"                                                            B.FIELDNAME)," +
					"                          '-'," +
					"                          -C_PAY_UTIL.GET_AMT_BY_EMPL_CAL_ID(A.EMPLID," +
					"                                                             A.CAL_RUN_ID," +
					"                                                             B.FIELDNAME)," +
					"                          0)) C_AMT" +
					"          FROM PS_C_WA_PAY_RSLT A," +
					"               (SELECT T3.BUSINESS_UNIT," +
					"                       T3.GP_PAYGROUP," +
					"                       T1.SEQNO         SEQNO1," +
					"                       T1.FP_ITEM_NAME  FP_ITEM_NAME1," +
					"                       T2.SEQNO         SEQNO2," +
					"                       T2.FP_ITEM_NAME  FP_ITEM_NAME2," +
					"                       T2.Fp_Operator   FP_OPERATOR2," +
					"                       T3.FP_ITEM_NAME  FP_ITEM_NAME3," +
					"                       T3.FIELDNAME," +
					"                       T3.FP_OPERATOR   FP_OPERATOR3" +
					"                  FROM PS_FP_PAY_FORMU_T3 T3," +
					"                       PS_FP_PAY_FORMU_T2 T2," +
					"                       PS_FP_PAY_FORMU_T1 T1" +
					"                 WHERE T3.BUSINESS_UNIT = T1.BUSINESS_UNIT" +
					"                   AND T3.GP_PAYGROUP = T1.GP_PAYGROUP" +
					"                   AND T3.FP_ITEM_ID = T1.FP_ITEM_ID" +
					"                   AND T3.BUSINESS_UNIT = T2.BUSINESS_UNIT" +
					"                   AND T3.GP_PAYGROUP = T2.GP_PAYGROUP" +
					"                   AND T3.FP_ITEM_ID = T2.FP_ITEM_ID" +
					"                   AND T3.FP_ITEM_ID2 = T2.FP_ITEM_ID2) B," +
					"               PS_GP_CAL_RUN C" +
					"         WHERE A.RSLT_SEG_NUM = 1" +
					"           AND B.GP_PAYGROUP =" +
					"               (decode((SELECT (SELECT 'Y'" +
					"                                 FROM ps_FP_PAY_SLIP_TBL x" +
					"                                WHERE X2.gp_paygroup = x.gp_paygroup" +
					"                                  AND business_unit = 'SHARE')" +
					"                         FROM ps_c_wa_pay_rslt X2" +
					"                        WHERE X2.emplid = A.EMPLID" +
					"                          AND X2.cal_run_id = A.cal_run_id" +
					"                          AND rownum = 1)," +
					"                       'Y'," +
					"                       A.GP_PAYGROUP," +
					"                       'SHARE'))" +

                    "   AND B.GP_PAYGROUP = '{}'" +
					"           AND A.CAL_RUN_ID = C.CAL_RUN_ID" +
					"           AND (C.RUN_FINALIZED_IND = 'Y' OR C.C_RUN_SRCH_SEC = 'Y')" +
					"         GROUP BY A.EMPLID," +
					"                  to_char(A.PRD_BGN_DT, 'yyyy')," +
					"                  A.CAL_RUN_ID," +
					"                  C.DESCR," +
					"                  B.SEQNO1," +
					"                  B.FP_ITEM_NAME1," +
					"                  B.SEQNO2," +
					"                  B.FP_ITEM_NAME2," +
					"                  B.FP_OPERATOR2)" +
                    "     WHERE EMPLID = '{}'" +
			"   AND CAL_RUN_ID = '{}'" +
			"   AND C_YEAR = '{}'" +
			"   AND DESCR = '{}'" +
					" GROUP BY EMPLID, C_YEAR, CAL_RUN_ID, DESCR, SEQNO1, FP_ITEM_NAME1";
			
//			"SELECT EMPLID" +
//			" , C_YEAR" +
//			" , CAL_RUN_ID" +
//			" , DESCR" +
//			" , SEQNO1" +
//			" , FP_ITEM_NAME1" +
//			" , SUM(decode(FP_OPERATOR2" +
//			" , '+'" +
//			" , C_AMT" +
//			" , '-' " +
//			" , -C_AMT " +
//			" , 0)) " +
//			"  FROM ( " +
//			" SELECT A.EMPLID " +
//			" , to_char(A.PRD_BGN_DT, 'yyyy') C_YEAR " +
//			" , A.CAL_RUN_ID " +
//			" , C.DESCR " +
//			" , B.SEQNO1 " +
//			" , B.FP_ITEM_NAME1 " +
//			" , B.SEQNO2 " +
//			" , B.FP_ITEM_NAME2 " +
//			" , B.Fp_Operator2 " +
//			" , SUM(decode(B.FP_OPERATOR3 " +
//			" , '+' " +
//			" , C_PAY_UTIL.GET_AMT_BY_EMPL_CAL_ID(A.EMPLID " +
//			" , A.CAL_RUN_ID " +
//			" , B.FIELDNAME) " +
//			" , '-' " +
//			" , -C_PAY_UTIL.GET_AMT_BY_EMPL_CAL_ID(A.EMPLID " +
//			" , A.CAL_RUN_ID " +
//			" , B.FIELDNAME) " +
//			" , 0)) C_AMT " +
//			"  FROM PS_C_WA_PAY_RSLT A " +
//			"  , ( " +
//			" SELECT T3.BUSINESS_UNIT " +
//			" , T3.GP_PAYGROUP " +
//			" , T1.SEQNO SEQNO1 " +
//			" , T1.FP_ITEM_NAME FP_ITEM_NAME1 " +
//			" , T2.SEQNO SEQNO2 " +
//			" , T2.FP_ITEM_NAME FP_ITEM_NAME2 " +
//			" , T2.Fp_Operator FP_OPERATOR2 " +
//			" , T3.FP_ITEM_NAME FP_ITEM_NAME3 " +
//			" , T3.FIELDNAME " +
//			" , T3.FP_OPERATOR FP_OPERATOR3 " +
//			"  FROM PS_FP_PAY_FORMU_T3 T3 " +
//			"  , PS_FP_PAY_FORMU_T2 T2 " +
//			"  , PS_FP_PAY_FORMU_T1 T1 " +
//			" WHERE T3.BUSINESS_UNIT = T1.BUSINESS_UNIT " +
//			"   AND T3.GP_PAYGROUP = T1.GP_PAYGROUP " +
//			"   AND T3.FP_ITEM_ID = T1.FP_ITEM_ID " +
//			"   AND T3.BUSINESS_UNIT = T2.BUSINESS_UNIT " +
//			"   AND T3.GP_PAYGROUP = T2.GP_PAYGROUP " +
//			"   AND T3.FP_ITEM_ID = T2.FP_ITEM_ID " +
//			"   AND T3.FP_ITEM_ID2 = T2.FP_ITEM_ID2) B, PS_GP_CAL_RUN C " +
//			" WHERE A.RSLT_SEG_NUM = 1 " +
////			"   AND A.GP_PAYGROUP = B.GP_PAYGROUP" +
//			"   AND B.GP_PAYGROUP = '{}'" +
//			"   AND A.CAL_RUN_ID = C.CAL_RUN_ID " +
//			"   AND C.RUN_FINALIZED_IND='Y' " +
//			"  GROUP BY A.EMPLID, to_char(A.PRD_BGN_DT, 'yyyy'), A.CAL_RUN_ID, C.DESCR, B.SEQNO1, B.FP_ITEM_NAME1, B.SEQNO2, B.FP_ITEM_NAME2, B.FP_OPERATOR2) " +
//			"   WHERE EMPLID = '{}'" +
//			"   AND CAL_RUN_ID = '{}'" +
//			"   AND C_YEAR = '{}'" +
//			"   AND DESCR = '{}'" +
//			"  GROUP BY EMPLID, C_YEAR, CAL_RUN_ID, DESCR, SEQNO1, FP_ITEM_NAME1";


	//工资小类查询
	String PAYROLL_DETAIL_LV2 =
            "SELECT A.EMPLID," +
                    "       to_char(A.PRD_BGN_DT, 'yyyy')," +
                    "       A.CAL_RUN_ID," +
                    "       C.DESCR," +
                    "       B.SEQNO1," +
                    "       B.FP_ITEM_NAME1," +
                    "       B.SEQNO2," +
                    "       B.FP_ITEM_NAME2," +
                    "       SUM(decode(B.FP_OPERATOR3," +
                    "                  '+'," +
                    "                  C_PAY_UTIL.GET_AMT_BY_EMPL_CAL_ID(A.EMPLID," +
                    "                                                    A.CAL_RUN_ID," +
                    "                                                    B.FIELDNAME)," +
                    "                  '-'," +
                    "                  -C_PAY_UTIL.GET_AMT_BY_EMPL_CAL_ID(A.EMPLID," +
                    "                                                     A.CAL_RUN_ID," +
                    "                                                     B.FIELDNAME)," +
                    "                  0))" +
                    "  FROM PS_C_WA_PAY_RSLT A," +
                    "       (SELECT T3.BUSINESS_UNIT," +
                    "               T3.GP_PAYGROUP," +
                    "               T1.SEQNO         SEQNO1," +
                    "               T1.FP_ITEM_NAME  FP_ITEM_NAME1," +
                    "               T2.SEQNO         SEQNO2," +
                    "               T2.FP_ITEM_NAME  FP_ITEM_NAME2," +
                    "               T2.Fp_Operator   FP_OPERATOR2," +
                    "               T3.FP_ITEM_NAME  FP_ITEM_NAME3," +
                    "               T3.FIELDNAME," +
                    "               T3.FP_OPERATOR   FP_OPERATOR3" +
                    "          FROM PS_FP_PAY_FORMU_T3 T3," +
                    "               PS_FP_PAY_FORMU_T2 T2," +
                    "               PS_FP_PAY_FORMU_T1 T1" +
                    "         WHERE T3.BUSINESS_UNIT = T1.BUSINESS_UNIT" +
                    "           AND T3.GP_PAYGROUP = T1.GP_PAYGROUP" +
                    "           AND T3.FP_ITEM_ID = T1.FP_ITEM_ID" +
                    "           AND T3.BUSINESS_UNIT = T2.BUSINESS_UNIT" +
                    "           AND T3.GP_PAYGROUP = T2.GP_PAYGROUP" +
                    "           AND T3.FP_ITEM_ID = T2.FP_ITEM_ID" +
                    "           AND T3.FP_ITEM_ID2 = T2.FP_ITEM_ID2) B," +
                    "       PS_GP_CAL_RUN C" +
                    " WHERE A.RSLT_SEG_NUM = 1" +
                    "   AND B.GP_PAYGROUP = (decode((SELECT (SELECT 'Y'" +
                    "                                         FROM ps_FP_PAY_SLIP_TBL x" +
                    "                                        WHERE X2.gp_paygroup = x.gp_paygroup" +
                    "                                          AND business_unit = 'SHARE')" +
                    "                                 FROM ps_c_wa_pay_rslt X2" +
                    "                                WHERE X2.emplid = A.EMPLID" +
                    "                                  AND X2.cal_run_id = A.cal_run_id" +
                    "                                  AND rownum = 1)," +
                    "                               'Y'," +
                    "                               A.GP_PAYGROUP," +
                    "                               'SHARE'))" +
                    "   AND A.CAL_RUN_ID = C.CAL_RUN_ID" +
                    "   AND (C.RUN_FINALIZED_IND = 'Y' OR C.C_RUN_SRCH_SEC = 'Y')" +
                    
                    "   AND A.EMPLID = '{}'" +
                    "   AND A.CAL_RUN_ID = '{}'" +
                    "   AND to_char(A.PRD_BGN_DT , 'yyyy') = '{}'" +
                    "   AND C.DESCR = '{}'" +
                    "   AND B.GP_PAYGROUP = '{}'" +
                    " GROUP BY A.EMPLID," +
                    "          to_char(A.PRD_BGN_DT, 'yyyy')," +
                    "          A.CAL_RUN_ID," +
                    "          C.DESCR," +
                    "          B.SEQNO1," +
                    "          B.FP_ITEM_NAME1," +
                    "          B.SEQNO2," +
                    "          B.FP_ITEM_NAME2";

//
//
//            "SELECT A.EMPLID " +
//			" , to_char(A.PRD_BGN_DT , 'yyyy') " +
//			" , A.CAL_RUN_ID " +
//			" , C.DESCR " +
//			" , B.SEQNO1 " +
//			" , B.FP_ITEM_NAME1 " +
//			" , B.SEQNO2 " +
//			" , B.FP_ITEM_NAME2 " +
//			" , SUM(decode(B.FP_OPERATOR3 " +
//			" , '+' " +
//			" , C_PAY_UTIL.GET_AMT_BY_EMPL_CAL_ID(A.EMPLID " +
//			" , A.CAL_RUN_ID " +
//			" , B.FIELDNAME) " +
//			" , '-' " +
//			" , -C_PAY_UTIL.GET_AMT_BY_EMPL_CAL_ID(A.EMPLID " +
//			" , A.CAL_RUN_ID " +
//			" , B.FIELDNAME) " +
//			" , 0)) " +
//			"  FROM PS_C_WA_PAY_RSLT A " +
//			"  , ( " +
//			" SELECT T3.BUSINESS_UNIT " +
//			" , T3.GP_PAYGROUP " +
//			" , T1.SEQNO SEQNO1 " +
//			" , T1.FP_ITEM_NAME FP_ITEM_NAME1 " +
//			" , T2.SEQNO SEQNO2 " +
//			" , T2.FP_ITEM_NAME FP_ITEM_NAME2 " +
//			" , T2.Fp_Operator FP_OPERATOR2 " +
//			" , T3.FP_ITEM_NAME FP_ITEM_NAME3 " +
//			" , T3.FIELDNAME " +
//			" , T3.FP_OPERATOR FP_OPERATOR3 " +
//			"  FROM PS_FP_PAY_FORMU_T3 T3 " +
//			"  , PS_FP_PAY_FORMU_T2 T2 " +
//			"  , PS_FP_PAY_FORMU_T1 T1 " +
//			" WHERE T3.BUSINESS_UNIT = T1.BUSINESS_UNIT " +
//			"   AND T3.GP_PAYGROUP = T1.GP_PAYGROUP " +
//			"   AND T3.FP_ITEM_ID = T1.FP_ITEM_ID " +
//			"   AND T3.BUSINESS_UNIT = T2.BUSINESS_UNIT " +
//			"   AND T3.GP_PAYGROUP = T2.GP_PAYGROUP " +
//			"   AND T3.FP_ITEM_ID = T2.FP_ITEM_ID " +
//			"   AND T3.FP_ITEM_ID2 = T2.FP_ITEM_ID2) B, PS_GP_CAL_RUN C " +
//			" WHERE A.RSLT_SEG_NUM = 1 " +
//			"   AND A.EMPLID = '{}'" +
//			"   AND A.CAL_RUN_ID = '{}'" +
//			"   AND to_char(A.PRD_BGN_DT , 'yyyy') = '{}'" +
//			"   AND C.DESCR = '{}'" +
////			"   AND A.GP_PAYGROUP = B.GP_PAYGROUP" +
//			"   AND B.GP_PAYGROUP = '{}'" +
//			"   AND A.CAL_RUN_ID = C.CAL_RUN_ID " +
//			"   AND C.RUN_FINALIZED_IND='Y' " +
//			"  GROUP BY A.EMPLID, to_char(A.PRD_BGN_DT, 'yyyy'), A.CAL_RUN_ID, C.DESCR, B.SEQNO1, B.FP_ITEM_NAME1, B.SEQNO2, B.FP_ITEM_NAME2";


	/**
	 * 查询入公司后的工作经历的sql
	 */
	String NOW_WORK_HIS_QUERY_SQL = new StringBuffer("SELECT " +
			"T.EMPLID, T.EMPL_RCD , T.EFFSEQ, T.BGN_DT, T.END_DT " +
			" , T.RS_FULL_NAME , T.C_DESCR, T.C_DESCR1, T.C_DESCR2, T.RS_JOB_LEVEL, T.C_JOB_LEVEL_P, T.C_JOBTYPE_DESC " +
			"  FROM ( " +
			" SELECT EMPLID " +
			" , BGN_DT " +
			" , EFFSEQ " +
			" , EMPL_RCD " +
			" , DECODE(DESCR " +
			" , '离职' " +
			" , BGN_DT " +
			" , END_DT) END_DT " +
			" , RS_FULL_NAME " +
			" , C_DESCR " +
			" , C_DESCR1 " +
			" , C_DESCR2 " +
			" , RS_JOB_LEVEL " +
			" , C_JOB_LEVEL_P " +
			" , C_JOBTYPE_DESC " +
			"  FROM ( " +
			" SELECT V.EMPLID " +
			" , V.EMPL_RCD " +
			" , V.EFFSEQ " +
			" , V.BGN_DT " +
			" , NVL(V.END_DT " +
			" , CASE WHEN lag(V.BGN_DT " +
			" , 1 " +
			" , NULL) over( " +
			"  ORDER BY V.BGN_DT DESC " +
			"  , V.EFFSEQ DESC) = V.BGN_DT THEN V.BGN_DT ELSE lag(V.BGN_DT " +
			"  , 1 " +
			"  , NULL) over( " +
			"  ORDER BY V.BGN_DT DESC " +
			"  , V.EFFSEQ DESC) - 1 END) AS END_DT " +
			"  , V.DESCR " +
			"  , V.RS_FULL_NAME " +
			"  , V.C_DESCR " +
			"  , V.C_DESCR1 " +
			"  , V.C_DESCR2 " +
			"  , V.RS_JOB_LEVEL " +
			"  , V.C_JOB_LEVEL_P " +
			"  , V.C_JOBTYPE_DESC " +
			"  FROM PS_C_JOB_HIS_VW V " +
			" WHERE V.EMPL_RCD = 0 " +
			"   AND V.EMPLID = '{}') W " +
			"  ORDER BY W.BGN_DT ASC) T " +
			"  ORDER BY T.BGN_DT DESC").toString();




	/**
	 * 字典数据列表查询sql
	 */
	public final static String DICT_QUERY_BYNAME_SQL = new StringBuilder(" SELECT A.FIELDVALUE, A.XLATLONGNAME, A.XLATSHORTNAME ")
	.append(" FROM PSXLATITEM A ")
	.append(" WHERE A.FIELDNAME ='{}' ")
	.append(" AND A.EFFDT = (SELECT MAX(A_ED.EFFDT) ")
	.append(" FROM PSXLATITEM A_ED ")
	.append(" WHERE A_ED.FIELDNAME = A.FIELDNAME ")
	.append(" AND A_ED.FIELDVALUE = A.FIELDVALUE ")
	.append(" AND A_ED.EFFDT <= SYSDATE) ")
	.append(" AND A.EFF_STATUS = 'A' ").toString();
	
	/**
	 * 字典数据值查询方法
	 */
	public final static String DICT_QUERY_BYVALUE_SQL = new StringBuilder(" SELECT A.FIELDVALUE, A.XLATLONGNAME, A.XLATSHORTNAME ")
	.append(" FROM PSXLATITEM A ")
	.append(" WHERE A.FIELDNAME ='{}' ")
	.append(" AND A.FIELDVALUE = '{}' ")
	.append(" AND A.EFFDT = (SELECT MAX(A_ED.EFFDT) ")
	.append(" FROM PSXLATITEM A_ED ")
	.append(" WHERE A_ED.FIELDNAME = A.FIELDNAME ")
	.append(" AND A_ED.FIELDVALUE = A.FIELDVALUE ")
	.append(" AND A_ED.EFFDT <= SYSDATE) ")
	.append(" AND A.EFF_STATUS = 'A' ").toString();
	
	
	/**
	 * 查询流程中的岗位名称
	 */
	public final static String JOBDESC_QUERY_BY_SETID_JOBCODE_SQL = new StringBuilder(" SELECT DESCR FROM PS_C_JOBCODE_VW A ")
	.append(" WHERE A.SETID = '{}' ")
	.append(" AND A.JOBCODE = '{}' ")
	.append(" AND A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_C_JOBCODE_VW A_ED ")
	.append(" WHERE A_ED.SETID = A.SETID ")
	.append(" AND A_ED.JOBCODE = A.JOBCODE ")
	.append(" AND A_ED.EFFDT <= SYSDATE) ").toString();
	
	
	/**
	 * 查询岗位列表
	 */
	public final static String JOBLIST_QUERY_BY_SETID_SQL = new StringBuilder(" SELECT JOBCODE,DESCR,RS_JOB_LEVEL,C_FUNCTION,C_FUNCTION_TYPE,C_FUNCTION_SUB ")
	.append(" FROM PS_C_JOBCODE_VW A ")
	.append(" WHERE A.SETID = '{}' ")
	.append(" AND A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_C_JOBCODE_VW A_ED ")
	.append(" WHERE A_ED.SETID = A.SETID ")
	.append(" AND A_ED.JOBCODE = A.JOBCODE ")
	.append(" AND A_ED.EFFDT <= SYSDATE) ").toString();
	
	
	/**
	 * 查询流程审批人员
	 */
	String PROCESSMAP_QUERY_BY_TRANSNUM_SQL = new StringBuilder(" SELECT S.C_STEP_SORT_NBR , S.C_STEP_NBR, S.DESCR , U.C_USER_NBR , '' AS SCORE ,")
	.append(" DECODE(U.ORIG_OPRID, ' ', (SELECT PER1.NAME FROM PS_PERSONAL_DATA PER1 WHERE PER1.EMPLID = P.EMPLID), ")
	.append(" ((SELECT PER1.NAME FROM PS_PERSONAL_DATA PER1 WHERE PER1.EMPLID = P.EMPLID) || ' (' || ")
	.append(" (SELECT PER2.NAME  FROM PS_PERSONAL_DATA PER2 WHERE PER2.EMPLID = OP.EMPLID) || '代理)')) AS EMPL_NAME, ")
	.append(" U.COMMENTS_4000,U.C_STEP_STATUS, (SELECT XLAT.XLATSHORTNAME FROM PSXLATITEM XLAT WHERE XLAT.FIELDNAME = 'C_STEP_STATUS' ")
	.append(" AND XLAT.FIELDVALUE = U.C_STEP_STATUS AND XLAT.EFFDT = (SELECT MAX(XLAT1.EFFDT) FROM PSXLATITEM XLAT1 ")
	.append(" WHERE XLAT1.FIELDNAME = XLAT.FIELDNAME  AND XLAT1.FIELDVALUE = XLAT.FIELDVALUE AND XLAT1.EFFDT <= SYSDATE)) AS STEP_STATUS_VALUE,U.LASTUPDDTTM ")
	.append(" FROM PS_C_APP_STEP_INST S, PS_C_APP_USER_INST U, PSOPRDEFN P, PSOPRDEFN OP ")
	.append(" WHERE S.C_TRANSACTION_NBR = U.C_TRANSACTION_NBR ")
	.append(" AND S.C_STEP_NBR = U.C_STEP_NBR ")
	.append(" AND P.OPRID = U.OPRID ")
	.append(" AND U.ORIG_OPRID = OP.OPRID(+) ")
	.append(" AND S.C_TRANSACTION_NBR = '{}' ")
	.append(" ORDER BY S.C_STEP_SORT_NBR, U.C_USER_NBR ").toString();


	/**
	 * 请假是否校验查询
	 */
	String LEAVE_CHECK_LEFT_SQL = "SELECT A.C_CHECK_BALANCE_SW " +
			"FROM PS_C_LEAVE_DEFN A WHERE A.C_LEAVE_TYPE ='{}'" +
			" ORDER BY A.EFFDT DESC";

	/**
	 * 部门经理获取
	 */
	String UNIT_MANAGER = "SELECT A.NAME " +
			"  FROM PS_C_JOB_CURR_VW A " +
			"  ,PS_DEPT_TBL B " +
			" WHERE A.SETID_JOBCODE=B.SETID_JOBCODE " +
			"   AND A.JOBCODE=B.C_DEPT_MGR_JOBCODE " +
			"   AND B.C_DEPT_LEVEL='110' " +
			"   AND B.SETID_JOBCODE='{}' " +
			"   AND A.EMPL_RCD=0 " +
			"   AND B.effdt = ( " +
			" SELECT MAX(C.effdt) " +
			"  FROM ps_dept_tbl C " +
			" WHERE B.setid = C.setid " +
			"   AND B.deptid = C.deptid " +
			"   AND C.effdt <= sysdate) " +
			"   AND B.eff_status = 'A' " +
			"   AND a.hr_status='A'";


	/**
	 * 查询流程标题
	 */
	String QUERY_WF_TITLE = "select a.descR254 from PS_C_APP_SET_AGT A,PS_C_APP_STEP_INST B " +
			"where A.c_app_id = B.C_APP_id and B.c_STEP_NBR = 1 and rownum<10 and B.C_TRANSACTION_NBR={}";
	/**
	 * 事业部经理
	 */
	String DEPT_MANAGER = "SELECT RS_UTIL.GET_PERSON_NAME(JOB.EMPLID) AS NAME" +
			"  FROM PS_DEPT_TBL D," +
			"       (SELECT *" +
			"          FROM PS_JOB A" +
			"         WHERE A.EFFDT = (SELECT MAX(C.EFFDT)" +
			"                            FROM PS_JOB C" +
			"                           WHERE C.EMPLID = A.EMPLID" +
			"                             AND C.EMPL_RCD = A.EMPL_RCD" +
			"                             AND C.EFFDT <= SYSDATE)" +
			"           AND A.EFFSEQ = (SELECT MAX(D.EFFSEQ)" +
			"                             FROM PS_JOB D" +
			"                            WHERE D.EMPLID = A.EMPLID" +
			"                              AND D.EMPL_RCD = A.EMPL_RCD" +
			"                              AND D.EFFDT = A.EFFDT)) JOB" +
			" WHERE D.EFFDT = (SELECT MAX(D1.EFFDT)" +
			"                    FROM PS_DEPT_TBL D1" +
			"                   WHERE D.SETID = D1.SETID" +
			"                     AND D.DEPTID = D1.DEPTID" +
			"                     AND D1.EFFDT <= SYSDATE)" +
			"   AND D.EFF_STATUS = 'A'" +
			"   AND D.C_DEPT_MGR_JOBCODE = JOB.JOBCODE" +
			"   AND D.C_DEPT_LEVEL = 120" +
			"   AND D.DEPTID = {}" +
			"   AND D.SETID = 'SHARE'" +
			" ORDER BY JOB.EMPL_RCD";

	/**
	 * 查询离职原因列表
	 */
	String ACTION_RESON_QUERY_SQL = new StringBuilder("SELECT A.ACTION_REASON, A.DESCR FROM PS_ACTN_REASON_TBL A ")
	.append(" WHERE A.ACTION = '{}' ")
	.append(" AND A.EFF_STATUS = 'A' ")
	.append(" AND A.EFFDT = (SELECT MAX(A_ED.EFFDT) ")
	.append(" FROM PS_ACTN_REASON_TBL A_ED ")
	.append(" WHERE A_ED.ACTION = A.ACTION ")
	.append(" AND A_ED.ACTION_REASON = A.ACTION_REASON ")
	.append(" AND A_ED.EFF_STATUS = 'A' ")
	.append(" AND A_ED.EFFDT <= SYSDATE) ").toString();
	
	
	/**
	 * 查询发薪单位
	 */
	String PAY_COMPANY_QUERY_SQL= new StringBuilder(" SELECT A.DESCR FROM PS_COMPANY_TBL A WHERE A.COMPANY = '{}' AND A.EFF_STATUS='A' ")
	.append(" AND A.EFFDT = (SELECT MAX(A_ED.EFFDT)  FROM PS_COMPANY_TBL A_ED WHERE A_ED.COMPANY = A.COMPANY ")
	.append(" AND A_ED.EFF_STATUS = 'A' AND A_ED.EFFDT <= SYSDATE)").toString();


	/**
	 * 查询薪资组
	 */
	String PYGRP_VW_QUERY_SQL = "SELECT A.DESCR FROM PS_C_PYGRP_VW A WHERE A.GP_PAYGROUP = '{}'";
	//社保缴纳地
	String SIL_LOC_QUERY_SQL = "SELECT A.RS_SIL_DESCR FROM PS_C_SIL_LOC_VW A WHERE A.RS_SIL_CD = '{}' AND A.EFF_STATUS='A'";
	//部门名称
	String PS_DEPT_QUERY_SQL = new StringBuilder(" SELECT A.DESCR FROM PS_DEPT_TBL A WHERE A.DEPTID = '{}' AND A.EFF_STATUS='A' ")
			.append(" AND A.EFFDT <= SYSDATE ORDER BY A.EFFDT DESC").toString();





	/**
	 * 查询社保单位
	 */
	 public final static String SI_COMPANY_QUERY_SQL= new StringBuilder("SELECT A.DESCR FROM PS_COMPANY_TBL A WHERE A.COMPANY = '{}' ")
	 .append(" AND A.EFF_STATUS='A' AND A.EFFDT = (SELECT MAX(A_ED.EFFDT) ")
	 .append(" FROM PS_COMPANY_TBL A_ED WHERE A_ED.COMPANY = A.COMPANY ")
	 .append(" AND A_ED.EFF_STATUS = 'A'  AND A_ED.EFFDT <= SYSDATE) UNION ")
	 .append(" SELECT A.DESCR FROM PS_C_SI_CMPY_TBL A WHERE A.COMPANY = '{}' ")
	 .append(" AND A.EFF_STATUS='A' AND A.EFFDT = (SELECT MAX(A_ED.EFFDT) ")
	 .append(" FROM PS_C_SI_CMPY_TBL A_ED  WHERE A_ED.COMPANY = A.COMPANY ")
	 .append(" AND A_ED.EFF_STATUS = 'A' AND A_ED.EFFDT <= SYSDATE) ").toString();
	 
	
	 /**
	  * 查询成本控制中心
	  */
	 String COST_CENTER_QUERY_SQL= new StringBuilder(" SELECT DESCR FROM PS_RS_COST_CTR_TBL A WHERE A.EFFDT = (SELECT MAX(A_ED.EFFDT) ")
	 .append(" FROM PS_RS_COST_CTR_TBL A_ED WHERE A_ED.RS_COST_CENTER = A.RS_COST_CENTER ")
	 .append(" AND A_ED.EFF_STATUS = 'A' AND A_ED.EFFDT <= SYSDATE) AND A.RS_COST_CENTER = '{}' ")
	 .append(" AND A.EFF_STATUS = 'A' ").toString();

	/**
	 * 获取部门全称
	 */
	String QUERY_DEPT_FULL_NAME_SQL = " SELECT RS_UTIL.GET_DEPT_FULLNAME('{}','{}',SYSDATE) AS FULLNAME FROM DUAL ";

	/**
	  * 获取业务单位描述
	  */
	 String QUERY_BUSINESS_NAME_SQL = " SELECT A.DESCR FROM PS_BUSUNIT_HR_VW A WHERE A.BUSINESS_UNIT = '{}' ";

	/**
	 * 获取岗位性质短描述
	 */
	String QUERY_JOB_TYPE_DESCSHORT_SQL = " SELECT A.DESCRSHORT FROM PS_C_POSIT_NAT_TBL A WHERE A.POSITION_NATURE = '{}' ";

	/**
	 * 获取人员岗位拼接描述信息
	 */
	String QUERY_JOB_JOINT_DESC_SQL = "SELECT NAME||'_'||C_BUSN_DESCR||'_'||DEPT_DESCR||'_'||JOBCODE_DESCR  AS NAME_C FROM PS_C_JOB_CURR_VW WHERE EMPLID='{}' AND EMPL_RCD='0'";

	/**
	 * 获取流程分权信息 分权引用标识，引用明细
	 */
	String QUERY_FQYY_INFO_SQL = "SELECT A.C_FQYY_FLAG,A.C_FQYY_DETAIL FROM PS_C_FQYY_SUB A WHERE A.C_TRANSACTION_NBR = '{}'";

	/**
	 * 获取无分权引用流程总数
	 */
	String CAL_NO_FQYY_SUM_SQL = "SELECT TO_CHAR(COUNT(*)) FROM  PS_C_WF_DRFT_HDR A ,PS_C_FQYY_SUB B WHERE A.C_WF_STATUS = 'A' AND A.C_TRANSACTION_NBR = B.C_TRANSACTION_NBR AND B.C_FQYY_FLAG = 'N'";

	/**
	 * 获取总流程数
	 */
	String CAL_PROCESS_TOTAL_SQL = "SELECT TO_CHAR(COUNT(*)) FROM  PS_C_WF_DRFT_HDR A ,PS_C_FQYY_SUB B WHERE A.C_WF_STATUS = 'A' AND A.C_TRANSACTION_NBR = B.C_TRANSACTION_NBR";

	/**
	 * 员工图片路径
	 */
	String EMPLPHOTO_STATIC_PATH = "/emplPhoto/";
	
	/**
	 * 下载的ftp文件路径
	 */
	String FTPFILE_STATIC_PATH = "/";
	
	/**
     * 字典值字段
     */
    Set<String> DICT_FIELD_SET = new HashSet<String>(Arrays.asList("sex","highestEducLvl","politicalStaChn"
    		,"rsBloodType","rsHealthStatus","marStatus","cBear","cEmplFrom","cEduciton","cDegree","cSaveStatus","rsLangCd","cLangLv"
    		,"cTechLv","rsJclbType","cFamilySex","cRelationFam","cTactPerSex","cRelationTact","effStatus","accountTypePye"
    		,"rsHirSource","rsHirStatus","cDeptLevel","cWfStatus","cWfType","cPrcType","cLevelAbility","cNoLevelAbility"
    		,"cWorkQualified","cWorkAttitude","cCmprEvalt","cStepStatus","cCertType","cCertMethod","cWfBsty","cWfTolt"
    		,"cSendType","cPhfStatus","cRecruitSource","highEduLvlMex","cAsaStyle","cYearRange","cStage","cLocation","cLeaveInterval"
    		,"cEnddttmMode","cLevaeUnit","cLeaveType"
			,"cTolType2","cHukType","cEmplFrom","cPfqType","cEmplSched","cRelationFam",
			"cGraType", "cJobType2","cZgzsType",
			"cXfrJoblevel", "cXfrJoblevelp","cInrecruitment","cGwh","cPptInterRslt","cYn4",
			"cPayPostType","cAllowanceFalg","cIncentiveFalg","cGuaPerFalg","cPerBonFalg","cMzyy","cRecCatgory","cRecAuth","cRecNature","cRecEducation","cIsDxs","cYesno2","cYesno"));

	/**
     * 特殊的单表字典值字段
     */
    Set<String> SINGLE_TABLE_DICT_SET = new HashSet<String>(Arrays.asList("nationalIdType","bankCd","country"
    		,"ethnicGrpCd","regRegion","cPrcType"));
    
    
    /**
     * 需要在请求是去除的字段参数
     */
    Set<String> NEED_REMOVE_PARAMETER_FROM_REQUEST = new HashSet<String>(Arrays.asList("hrsRowAddDttm","hrsRowAddOprid","hrsRowUpdDttm"
    		,"hrsRowUpdOprid"));
    
    
    /**
     * 不需要登录的url
     */
    public static final Set<String> NOT_NEED_USER_LOGIN_URI= new HashSet<String>(Arrays.asList("/api/hr/bankEcTbl/getOne","/api/hr/bankEcTbl/getAll"
    		,"/api/hr/countryTbl/getOne","/api/hr/countryTbl/getAll","/api/hr/ethnicGrpTbl/getOne","/api/hr/ethnicGrpTbl/getAll","/api/hr/nidType1Vw/getOne"
    		,"/api/hr/nidType1Vw/getAll","/api/hr/school/getAll","/api/hr/regRegionTbl/getOne","/api/hr/regRegionTbl/getAll","/api/hr/sysdict/getOne","/api/hr/sysdict/getAll"));

	/**
	 * 流程表名对应关系
	 */
	public static Map<String,String> processMap = Collections.unmodifiableMap(new HashMap<String,String>(){
		{
			put("goOut","PS_C_WF_GOUT_HDR");
			put("vacation","PS_C_WF_PLAVE_HDR");
			put("unvacation","PS_C_WF_CLEA_HDR");
			put("overTime","PS_C_WF_WKOT_HDR");
			put("formal","PS_C_WF_PRC_HDR");
			put("wastage","PS_C_WF_TER_HDR");
			put("employment","PS_C_WF_DRFT_HDR");
			put("mobilization","PS_C_WF_XFR_HDR");
			put("recruitment","PS_C_WF_REC_HDR");
		}
	});

	/**
	 * 查询是否体系负责人
	 */
    String QUERY_AUX_SYSTEM_INFO_SQL = "SELECT 'Y' " +
			"  FROM ps_C_DEPT_ROLE a " +
			"  ,ps_C_DEPT_ROLE_CFG b " +
			" WHERE a.effdt = ( " +
			" SELECT MAX(aa.effdt) " +
			"  FROM ps_C_DEPT_ROLE aa " +
			" WHERE a.setid = aa.setid " +
			"   AND a.deptid = aa.deptid " +
			"   AND aa.effdt <= sysdate " +
			"   AND aa.eff_status = 'A') " +
			"   AND a.eff_status = 'A' " +
			"   AND a.setid = b.setid " +
			"   AND a.deptid = b.deptid " +
			"   AND b.oprid =  '{}'" +
			"   AND b.c_role_id = 'AUX005' " +
			"   AND a.effdt = b.effdt";
}
